using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Common.Database
{
    /// <summary>
    /// 
    /// </summary>
    public class DatabaseTraffic
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static List<DbLogonUser> GetAllLogonUsers(string connStr)
        {
            List<DbLogonUser> logonUsers=new List<DbLogonUser>();
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "exec sp_who2";
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    DbLogonUser logonUser=new DbLogonUser();
                    logonUser.LoginName = reader["Login"].ToString();
                    logonUser.HostName = reader["HostName"].ToString();
                    logonUser.DbName = reader["DbName"].ToString();
                    logonUser.CommandName = reader["Command"].ToString();
                    logonUser.CpuTime = int.Parse(reader["CpuTime"].ToString());
                    logonUser.DiskIO = int.Parse(reader["DiskIO"].ToString());
                    logonUser.LastAccessTime = DatabaseTraffic.ParseDbTime((reader["LastBatch"].ToString()));
                    logonUser.ProgramName = reader["ProgramName"].ToString();
                    logonUsers.Add(logonUser);
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return logonUsers;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="domainName"></param>
        /// <param name="dbName"></param>
        /// <param name="since"></param>
        /// <returns></returns>
        public static List<DbLogonUser> GetUsersWhoAccessedDBSince(string connStr, string domainName, string dbName, DateTime since)
        {
            List<DbLogonUser> allLogonUsers = DatabaseTraffic.GetAllLogonUsers(connStr);
            List<DbLogonUser> userActivities=new List<DbLogonUser>();
            if(allLogonUsers !=null && allLogonUsers.Count>0)
            {
                foreach(DbLogonUser dbUser in allLogonUsers)
                {
                    if(dbUser.LoginName.ToLower().StartsWith(domainName.ToLower()))
                    {
                        if(dbUser.DbName.ToLower()==dbName.ToLower() && dbUser.LastAccessTime>=since)
                        {
                            userActivities.Add(dbUser);
                        }
                    }
                }
            }
            return userActivities;
        }

        private static DateTime ParseDbTime(string dbTime)
        {
            Regex regex = new Regex("([0-9][0-9])/([0-9][0-9])\\s+([0-9][0-9]):([0-9][0-9]):([0-9][0-9])", RegexOptions.IgnoreCase);
            Match match = regex.Match(dbTime);

            int year = DateTime.Now.Year;
            int month = DateTime.Now.Month;
            int day = DateTime.Now.Day;
            int hour = DateTime.Now.Hour;
            int min = DateTime.Now.Second;
            int sec = 0;

            if(match.Success)
            {
                month = int.Parse(match.Groups[1].Value);
                day = int.Parse(match.Groups[2].Value);
                hour = int.Parse(match.Groups[3].ToString());
                min = int.Parse(match.Groups[4].Value);
                sec = int.Parse(match.Groups[5].Value);
            }
            return new DateTime(year, month, day, hour, min, sec);
        }
    }
}
